General Database Queries And Practices

GENERAL DATABASE QUERIES AND PRACTICES

The following query retrieves "2" highest-paid employees FROM each Department:

SELECT deptno, empno, sal
FROM emp e
WHERE 2 > ( SELECT COUNT(e1.sal)
FROM emp e1
WHERE e.deptno = e1.deptno AND e.sal < e1.sal
)
ORDER BY 1,3 DESC;
SELECT * FROM EMP A
WHERE &N > ( SELECT COUNT(DISTINCT SAL) FROM EMP B WHERE B.SAL>A.SAL)
ORDER BY A.SAL DESC;
SELECT * FROM
(SELECT * FROM emp ORDER BY salary DESC) WHERE ROWNUM <3

Query that will display the total no. of employees, and of that total the number who were hired in 1980, 1981, 1982, and 1983. Give the appropriate column headings.

I am looking at the following output. We need to stick to this format.

SELECT COUNT (*), COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno))
"1980",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1981', empno))
"1981",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1982', empno))
"1982",
COUNT (DECODE (TO_CHAR (hiredate, 'YYYY'), '1983', empno))
"1983"
FROM emp;

Query for listing deptno, ename, sal, SUM(sal in that dept) :

SELECT a.deptno, ename, sal, (SELECT SUM(sal) FROM emp b WHERE a.deptno =b.deptno)
FROM emp a
ORDER BY a.deptno;

OUTPUT :

=======
DEPTNO		ENAME	SAL	SUM (SAL)
=========		=======	====	=========
10		KING	5000	11725
30		BLAKE	2850	10900
10		CLARK	2450	11725
10		JONES	2975	11725
30		MARTIN	1250	10900
30		ALLEN	1600	10900
30		TURNER	1500	10900
30		JAMES	950	10900
	30	WARD	2750	10900

20	SMITH	8000	33000
20	SCOTT	3000	33000
20	MILLER	20000	33000

Inclined to build a profession as MicroStrategy Developer? Then here is the blog post on, explore MicroStrategy Training

Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job for all departments, giving each column an appropriate heading.

The output is as follows - we need to stick to this format :

Job	Dept 10	Dept 20	Dept 30
Total
----------	---------------	-------------	-------------	-
--------
ANALYST		6000
6000
CLERK	1300	1900	950
4150
MANAGER	2450	2975	2850
8275
PRESIDENT	5000

5000
SALESMAN			5600
5600

SELECT job "Job", SUM (DECODE (deptno, 10, sal)) "Dept 10",
SUM (DECODE (deptno, 20, sal)) "Dept 20",
SUM (DECODE (deptno, 30, sal)) "Dept 30",
SUM (sal) "Total"
FROM emp GROUP BY job ;

 4^th Top Salary of all the employees 

SELECT DEPTNO, ENAME, SAL
FROM EMP A
WHERE
3 = (SELECT COUNT(B.SAL) FROM EMP B WHERE A.SAL < B.SAL) ORDER BY SAL DESC;
SELECT * FROM EMP A
WHERE &N-1 = (SELECT COUNT (DISTINCT SAL) FROM EMP B WHERE B.SAL>A.SAL);
Alternate
SELECT ename, deptno, sal
ROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM < N;

 Retrieving the 5th row FROM a table :

ELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID = (SELECT ROWID FROM EMP
WHERE ROWNUM <= 5 MINUS
SELECT ROWID FROM EMP WHERE ROWNUM < 5

Display the alternate row from the table.

select rownum,empno,ename from emp
group by rownum,empno,ename
having mod(rownum,2)=0;

OR

select * from emp
where rowid in (select decode(mod(rownum,2),0,rowid) from emp)

OR

For Even Rownumber
Select * from emp
Where (rowid,0) in (select rowid, mod(rownum,2) from emp)
For Odd Rownumber
Select * from emp
Where (rowid, 1) in (select rowid, mod(rownum,2) from emp)

Tree Query :

Name Null? Type
-------------------------------------------------------------------
SUB NOT NULL VARCHAR2(4)
SUPER VARCHAR2(4)
PRICE NUMBER(6,2)
SELECT sub, super
FROM parts
CONNECT BY PRIOR sub = super
START WITH sub = 'p1';

Eliminate duplicates rows in a table :

DELETE FROM table_name A
WHERE ROWID > ( SELECT min(ROWID) FROM table_name B WHERE A.col = B.col);
OR
DELETE FROM table_name A
WHERE ROWID < ( SELECT max(ROWID) FROM table_name B WHERE A.col =
B.col);

Displaying EVERY 4th row in a table : (If a table has 14 rows, 4,8,12 rows will be selected)

SELECT *FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
FROM emp);
ALTERNATE
SELECT * FROM TB_CNR014_DIM_BRAND
WHERE ROWID IN (SELECT DECODE(MOD(BRAND_ID, 4), 0, ROWID) FROM
TB_CNR014_DIM_BRAND)

MicroStrataegy Advanced Interview Questions

Top N rows FROM a table : (Displays top 9 salaried people)

SELECT ename, deptno, sal
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM < 10;

How does one count/sum RANGES of data values in a column? A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z).

COUNT(DECODE(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
COUNT(DECODE(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
COUNT(DECODE(greatest(f1,29), least(f1, 0), 1, 0)) "Range 00-29"
FROM  my_table
GROUP BY f2;
Correct Answer: Do not Consider 0
SELECT COUNT(*),
count(decode( greatest(field_3,20),least(field_3,49),1)) AS "Range 20-50",
count(decode( greatest(field_3,50),least(field_3,59),1)) AS "Range 50-60",
count(decode( greatest(field_3,60),least(field_3,69),1)) AS "Range 60-70",
count(decode( greatest(field_3,90),least(field_3,99),1)) AS "Range 90-100"
FROM test_mapping

For equal size ranges it migth be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).

SELECT ename "Name", sal "Salary",
DECODE( TRUNC(sal/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.3) "Tax rate"
FROM emp;

How does one count different data values in a column?

COL NAME DATATYPE
----------------------------------------
DNO NUMBER
SEX CHAR
SELECT dno, SUM(DECODE(sex,'M',1,0)) MALE,
SUM(DECODE(sex,'F',1,0)) FEMALE,
COUNT(DECODE(sex,'M',1,'F',1)) TOTAL
FROM t1
GROUP BY dno;

Query to get the product of all the values of a column :

SELECT EXP(SUM(LN(col1))) FROM srinu;

Query to display only the duplicate records in a table:

SELECT num
FROM satyam
GROUP BY num
HAVING COUNT(*) > 1;

Query for getting the following output as many numbers of rows in the table :

*
**
***
****
*****
SELECT RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM srinu1;

Find the date of last Thursday of the Month

select next_day(last_day(sysdate)-7,’thursday’) from dual;

Update multiple rows in using a single update statement.

Update emp set sal=
Case job
When ‘CLERK’ then sal+500
When ‘SALESMAN’ then sal+600
When ‘MANAGER’ then sal+2000
Else sal
End;
OR
Update emp set sal=
Decode(job,’CLERK’,sal+200,’SALESMAN’,sal+300,’MANAGER’,sal+500);

Show the ename of all employees together with the number of completed months that they have been employed.

SELECT ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) YEAR,
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) MONTH FROM EMP

Display the following output:

		DNO	ASAL		ECOUNT	ENAME	SAL JOB
---- ---------- ----------					----------  ----------		---------
10			2916.66667		3 CLARK 2450 MANAGER

	KING			5000 PRESIDENT
	MILLER			1300 CLERK
3614.8		5 SMITH			4000 CLERK
	ADAMS			1100 CLERK
	FORD			3000 ANALYST
	SCOTT			6999 ANALYST
	JONES			2975 MANAGER
1400	6 ALLEN			600 SALESMAN
	BLAKE			2850 MANAGER
	MARTIN			1250 SALESMAN
	JAMES			950 CLERK
	TURNER			1500 SALESMAN
	WARD			1250 SALESMAN

SOLUTION:

BREAK ON DNO ON ASAL ON ECOUNT SKIP 1
select d.deptno dno,avg(d.sal) asal,count(d.ename) ecount, e.ename,e.sal,e.job from emp d,emp e where d.deptno=e.deptno group by d.deptno,e.ename,e.sal,e.job

OR

SELECT DNO,ASAL,ECOUNT,E.ENAME,E.SAL,E.JOB FROM (SELECT DEPTNO DNO, AVG(SAL) ASAL,COUNT(ENAME) ECOUNT FROM EMP GROUP BY DEPTNO),EMP E WHERE DNO=E.DEPTNO;

Display the first and second higest sal from each deprtment

select deptno,max(sal) from emp group by deptno union

select deptno,max(sal) from (select deptno,sal from emp minus select deptno,max(sal) from emp group by deptno) group by deptno

or

select sal from emp e1 where &n>=(select count(distinct sal) from emp e2 where e1.sal<=e2.sal and e1.deptno=e2.deptno)

Date related Query.

• Find out the current date:

select sysdate from dual;

• Find out the date of the next dateSelect sysdate+1 from dual;
• Find out the one hour from noSelect to_char(sysdate+1/24,’hh’) from dual;
• Find out the three hours from now

Select to_char(sysdate+3/24,’hh’) from dual;

• Find out the half an hour from now

Select to_char(sysdate+1/48,’hh:mi’) from dual;

• Find out the 10 minutes from now

Select to_char(sysdate+10/1440,’mi’) from dual;

• Find out the 10 second from now

Select to_char(sysdate+10/86400,’SS’) from dual;

• Find out the tomorrow at the midnight 12:00

Select to_char(trunc(sysdate+1),’dd-mm-yy-/hh:mi:ss’) from dual;

• Find the date of tomorrow at 8:00 A.M.

Select to_char(trunc(sysdate+1)+8/24) from dual;

• Find out the next Monday at 12:00 noon

Select next_day(trunc(sysdate),'Monday')+12/24 from dual;

• Find out the first day of next month at 12 mid night

Select trunc(last_day(sysdate)+1) from dual;

• Find out the first day of current month

Select last_day(add_months(sysdate,-1))+1 from dual;

Display the bytes occupied by each ename in the emp table.

Select vsize(ename) from emp;

Display our age in days.

Select to_date(sysdate)-to_date('14-Aug-1980') Days from dual;

Display our age in a year.

select trunc(trunc(months_between(sysdate,'14-aug-80'))/12) year from dual;

Display the emp whose sal is greater than their manager.

select e.ename from emp e,emp m where e.mgr=m.empno and e.sal>m.sal;

Display the emp who are working in the same dept where his manager is working.

select e.ename from emp e,emp m where e.mgr=m.empno and e.deptno=m.deptno;

Display the ename whose manager is blake.

select e.ename from emp e,emp m where e.mgr=m.empno and m.ename=’BLAKE’;

Find the date for the nearest Saturday after the current date.

Select next_day(sysdate,’saturday’) from dual;
DDL Queries.

Rename the column name from the table.

alter table ddl rename column empno to eno;

Rename the table name

rename ddl to emp;

Drop the column from any table

alter table ttl drop column sal

Add the column in the table.

Alter table ttl add(sal number(4));

Add constraint in the table.

Alter table ttl add(empno number primary key);

Count the No. of Column from particular table.

select count(*) from cols where table_name='EMP';

Insert data from one table to another table.

Insert into emp1 select * from emp;

Create table structure from another table.

Create table emp1 as select * from emp;

Create index command

create index i1 on ttl(empno);
create bitmap index bi on ttl(mgr);
create index i2 on ttl(job) online;

Create cluster command.

Create cluster c1 (empno number);

Multi-table update using a trigger.

create or replace trigger multi_table_update
after update of sal on emp
for each row
begin
update emp1 set sal=:new.sal where empno=7369;
update emp2 set sal=:new.sal where empno=7369;
end;
/
DROP SEQUENCE S1;
CREATE SEQUENCE S1
START WITH 1
INCREMENT BY 1
MAXVALUE 15000
MINVALUE 1
NOCACHE
NOCYCLE;
EXEC PROCEDURE_SCD2;
SELECT * FROM PROC_SCD2;

Query for deleting alternate even rows FROM a table :

DELETE
FROM srinu
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);

Query for deleting alternate odd rows FROM a table :

DELETE
FROM srinu
WHERE (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2)
FROM srinu);

SELECT Query for counting No of words:

SELECT ename,
 NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRS
 TUVWXYZ'' ',' @'),' ',''))+1,1) word_length
 FROM emp;
 Explanation :
 TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','
 @') -- This will translate all the characters FROM A-Z including a single quote to aspace. It will also translate a space to a @.

REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ','@'),' ','') -- This will replace every space with nothing in the above result.

LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'' ',' @'),' ',''))+1 --

This will give u the count of @ characters in the above result.

Alternate:

SELECT length('samir') - length(REPLACE('samir','r','')) FROM dual;

Function to check for a leap year:

CREATE OR REPLACE FUNCTION is_leap_year (p_date IN DATE) RETURN VARCHAR2
AS
v_test DATE;
BEGIN
v_test := TO_DATE ('29-Feb-' || TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');
RETURN 'Y';
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END is_leap_year;
SQL> SELECT hiredate, TO_CHAR (hiredate, 'Day') weekday
FROM emp
WHERE is_leap_year (hiredate) = 'Y';

Query for removing all non-numeric :

SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz- ','')
FROM DUAL;

Query for translating column values to INITCAP :

SELECT
TRANSLATE(INITCAP(temp),
SUBSTR(temp, INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))
FROM srinu1;

Function for displaying Numbers in Words:

SELECT TO_CHAR( TO_DATE( SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;

Only up to integers from 1 to 5373484

Alternate Query for DECODE function :

SELECT case
WHEN sex = 'm' THEN 'male'
WHEN sex = 'f' THEN 'female'
ELSE 'unknown'
END
FROM mytable

Create table adding Constraint to a date field to SYSDATE or 3 months later:

CREATE TABLE srinu(dt1 date DEFAULT SYSDATE, dt2 date,
CONSTRAINT check_dt2 CHECK ((dt2 >= dt1) AND (dt2 <=
ADD_MONTHS(SYSDATE,3)));

Query to list all the suppliers who supply all the parts supplied by supplier 'S2' :

SELECT DISTINCT a.SUPP
FROM ORDERS a
WHERE a.supp != 'S2'
AND a.parts IN
(SELECT DISTINCT PARTS FROM ORDERS WHERE supp = 'S2')
GROUP BY a.SUPP
HAVING
COUNT(DISTINCT a.PARTS) >=
(SELECT COUNT(DISTINCT PARTS) FROM ORDERS WHERE supp = 'S2');

Table: orders SUPP                  PARTS ------------------ -------

S1	P1
S1	P2
S1	P3
S1	P4
S1	P5

S1	P6
S2	P1
S2	P2
S3	P2
S4	P2
S4	P4
S4	P5

Query to get the last Sunday of any month :

SELECT NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')
FROM DUAL;
SELECT NEXT_DAY(to_date(LAST DAY OF THE MONTH)-7, 'sunday') FROM dual

Query to get all those who have no children themselves:

table data: id       

name parent_id

-------------------------------

a NULL - the top-level entry

b 1 - a child of 1

3	c	1
4	d	2 - a child of 2
5	e	2
6	f	3
7	g	3
8	h	4
9	i	8

10       j          9

SELECT ID
FROM MY_TABlE
WHERE PARENT_ID IS NOT NULL
MINUS
SELECT PARENT_ID
FROM MY_TABlE;

Query to SELECT last N rows FROM a table :

SELECT empno FROM emp WHERE ROWID in
(SELECT ROWID FROM emp
MINUS
SELECT ROWID FROM emp WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM emp));
SELECT * FROM test_mapping WHERE ROWNUM < 11
MINUS
SELECT * FROM test_mapping WHERE ROWNUM < 9

SELECT with variables:

CREATE OR REPLACE PROCEDURE disp
AS
xTableName varchar2(25):='emp';
xFieldName varchar2(25):='ename';
xValue NUMBER;
xQuery varchar2(100);
name varchar2(10) := 'CLARK';
BEGIN
xQuery := 'SELECT SAL FROM ' || xTableName || ' WHERE ' || xFieldName ||
' = ''' || name || '''';
DBMS_OUTPUT.PUT_LINE(xQuery);
EXECUTE IMMEDIATE xQuery INTO xValue;
DBMS_OUTPUT.PUT_LINE(xValue);
END;

Query to get the DB Name:

SELECT name FROM v$database;

Getting the current default schema :

SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;

Query to get all the column names of a particular table :

SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = 'ORDERS';

How do I spool only the query result to a file in SQLPLUS:

Place the following lines of code in a file and execute the file in SQLPLUS :

set heading off
set feedback off
set colsep ' '
set termout off
set verify off
spool c:srini.txt
SELECT empno,ename FROM emp; /* Write your Query here */
spool off
/

Query for getting the current SessionID :

SELECT SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;

Query to display rows FROM m to n:

To display rows 5 to 7 :

SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID IN
(SELECT ROWID FROM EMP
WHERE ROWNUM <= 7
MINUS
SELECT ROWID FROM EMP
WHERE ROWNUM < 5);
OR
SELECT ename
FROM emp
GROUP BY ROWNUM, ename
HAVING ROWNUM > 1 and ROWNUM < 3;

Query to count no. Of columns in a table:

SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';

Procedure to increase the buffer length :

dbms_output.enable(4000); /*allows the output buffer to be increased to the
specified number of bytes */
DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/

Inserting an & symbol in a Varchar2 column:

Set the following to some other character. By default, it is &.

set define '~'

How do you remove Trailing blanks in a spooled file :

Change the Environment Options Like this :

set trimspool on

set timeout on

Differences between SQL and MS-Access :

Difference 1:

• Oracle: select name from table1 where name like 'k%';
• Access: select name from table1 where name like 'k*';

Difference 2:

• Access: SELECT TOP 2 name FROM Table1;
• Oracle: will not work there is no such TOP keyword.

Query to display all the children, sub children of a parent :

SELECT organization_id,name
FROM hr_all_organization_units
WHERE organization_id in
(
SELECT ORGANIZATION_ID_CHILD FROM PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD = (SELECT organization_id
FROM hr_all_organization_units
WHERE name = 'EBG Corporate Group'));

Query to display a random number between any two given numbers :

SELECT DBMS_RANDOM.VALUE (1,2) FROM DUAL;

How can I get the time difference between two date columns :

SELECT
FLOOR((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
ROUND((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600 -
(FLOOR((((date1-date2)*24*60*60) -
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
FROM my_table;

Using INSTR and SUBSTR

I have this string in a column named location

LOT 8 CONC3 RR

Using instr and substr, I want to take whatever value follows LOT and put it into a different column and whatever value follows CONC and put it into a different column

select substr('LOT 8 CONC3 RR',4,instr('LOT 8 CONC3 RR','CONC')-4) from
dual;
select substr('LOT 8 CONC3 RR',-(length('LOT 8 CONC3 RR')-(instr('LOT 8
CONC3 RR','CONC')+3)))
from dual

View procedure code

select text from all_source where name = 'X'
order by line;
select text from user_source where name = 'X'
select text from user_source where type = 'procedure' and
name='procedure_name';
select name,text from dba_source where name='ur_procedure'
and owner='scott';

To convert signed number to number in oracle

select to_number('-999,999.99', 's999,999.99') from dual; -999,999.99
select to_number('+0,123.45', 's999,999,999.99') from dual; 123.45
select to_number('+999,999.99', 's999,999.99') from dual; 999,999.99

Columns of a table

select column_name from user_tab_columns where TABLE_NAME = 'EMP'
select column_name from all_tab_columns where TABLE_NAME = 'EMP'
select column_name from dba_tab_columns where TABLE_NAME = 'EMP'
select column_name from cols where TABLE_NAME = 'EMP'

Delete rows conditionally

I have a table have

a,b,c field,

a,b should be unique, and leave max(c) row in.

How can I delete other rows?

delete from 'table'

where (a,b,c) not in (select a,b,max(c) from 'table' group by a,b);

Double quoting a Single quoted String

declare
-- we need one here to get a single quote into the variable
v_str varchar2 (20) := 'O''reilly''s';
begin
DBMS_OUTPUT.PUT_LINE ( 'original single quoted v_str= ' || v_str );
v_str := replace(v_str, '''', '''''');
DBMS_OUTPUT.PUT_LINE ( 'after double quoted v_str= ' || v_str );
end;
SQL> /
original single quoted v_str= O'reilly's
after double quoted v_str= O''reilly''s

Time Conversion

CREATE OR REPLACE FUNCTION to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC (i_days)) &#124&#124 ' days ' &#124&#124
TO_CHAR (TRUNC (SYSDATE) + MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
select to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002 11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from
dual;

Table comparison

The table is both the schemas should have the same structure. The data in it could be the same or different

a-b and b-a

select * from a.a minus select * from b.a and select * from b.a minus select *

from a.a

Running Jobs

select * from user_jobs;
exec dbms_job.remove(job_no);

Switching Columns

Update tblname
Set column1 = column2,
Column2 = column1;

Replace and Round

I have the number e.g. 63,9823874012983 and I want to round it to 63,98 and at the

same time change the, to a.

select round(replace('63,9823874012983',',','.'),2) from dual;

First date of the year

select trunc(sysdate, 'y') from dual;
01-jan-2002
last year this month through a select statement
select add_months(sysdate, -12) from dual;
05-APR-01

Create a Sequence

create sequence sh increment by 1 start with 0;

Current Week

select next_day(sysdate-7,'SUNDAY'), next_day(sysdate,'SATURDAY') from dual;
NEXT_DAY( NEXT_DAY(
--------- ---------
07-APR-02 13-APR-02

For an in-depth understanding of MicroStrategy click on

• MicroStrategy Tutorials
• MicroStrategy Reporting
• MicroStrategy Architect
• MicroStrategy DataWarehousing